import string

import duckdb, io
import pandas as pd
from config import ini_op
from openpyxl.styles import Alignment, PatternFill, Border, Side
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection


# 创建 NW_SZY 表
def create_table():
    con = get_connection()
    con.execute("""
        CREATE TABLE IF NOT EXISTS NW_SZY (
          uuid VARCHAR(32) PRIMARY KEY,
          dwbh VARCHAR(100),
          model VARCHAR(200),
          xhbh VARCHAR(100),
          xhmc VARCHAR(1000),
          fybh VARCHAR(100),
          fymc VARCHAR(100),
          je DECIMAL
        )
    """)
    con.close()

# 获取所有记录
def get_all_data():
    con = get_connection()
    query = "SELECT * FROM NW_SZY"
    result = con.execute(query).fetchall()
    con.close()
    return result

# 按条件获取记录
def get_data_by_condition(dwbh, model):
    con = get_connection()
    result = con.execute("SELECT * FROM NW_SZY "
                         " WHERE dwbh = ? and model = ? ",
                         (dwbh, model)).fetchall()
    con.close()
    return result


# 根据 ID 获取单条记录
def get_data_by_uuid(uuid):
    con = get_connection()
    query = "SELECT * FROM NW_SZY WHERE uuid = ?"
    result = con.execute(query, (uuid,)).fetchone()
    con.close()
    return result

# 添加记录
def add_data(uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, je):
    con = get_connection()
    con.execute("""
        INSERT INTO NW_SZY (uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, je)
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, je))
    con.close()

# 更新记录
def update_data(uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, je):
    con = get_connection()
    con.execute("""
        UPDATE NW_SZY
        SET dwbh = ?, model = ?, qzq_type = ?, xhbh = ?, xhmc = ?, fybh = ?, fymc = ?, je = ?
        WHERE uuid = ?
    """, (dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, je, uuid))
    con.close()

# 删除记录
def delete_data(uuid):
    con = get_connection()
    con.execute("DELETE FROM NW_SZY WHERE uuid = ?", (uuid,))
    con.close()


# 删除记录
def delete_data_by_model(p_dwbh, p_model, p_qzq_type):
    con = get_connection()
    con.execute("DELETE FROM NW_SZY WHERE dwbh = $p_dwbh and model = $p_model and ($p_qzq_type is null or qzq_type = $p_qzq_type)",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type})
    con.close()

# 删除记录
def delete_data_by_xh_and_fy(p_dwbh, p_model, p_qzq_type, p_xhbh, p_fybh):
    con = get_connection()
    con.execute("DELETE FROM NW_SZY "
                " WHERE dwbh = $p_dwbh and model = $p_model and ($p_qzq_type is null or qzq_type = $p_qzq_type) "
                "   and xhbh = $p_xhbh and fybh = $p_fybh",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type, 'p_xhbh':p_xhbh, 'p_fybh':p_fybh})
    con.close()

def download_excel_bak(p_dwbh, p_model, p_qzq_type):
    con = get_connection()
    # 将查询结果加载到 Pandas DataFrame
    df = con.execute("SELECT xhmc as 型号, fymc as 费用, je as 金额"
                " FROM NW_SZY "
                "WHERE dwbh = $p_dwbh and model = $p_model "
                "  and ($p_qzq_type is null or qzq_type = $p_qzq_type)",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchdf()

    # 设置自定义的 Excel 表头
    # 假设查询结果的列是 ['column1', 'column2', 'column3']，你可以自定义列名
    # custom_columns = ['型号', '费用', '金额']  # 自定义表头

    # 如果需要，可以重命名列
    # df.columns = custom_columns  # 将查询结果的列名替换为自定义列名

    df_pivot = df.pivot_table(index='型号', columns='费用', values='金额', aggfunc='sum')
    # 使用 Pandas 将 DataFrame 保存为 Excel 文件
    excel_dir = '/backend/file/szy/'  # 生成临时文件路径
    excel_filename = 'szy.xlsx'  # 生成临时文件路径
    excel_path = excel_dir + excel_filename
    df_pivot.to_excel(excel_path, index=False, header=True, engine='openpyxl')  # 保存为 Excel 文件

    # 关闭数据库连接
    con.close()
    return [excel_filename, excel_path]

def check_szy_ready(p_dwbh, p_model, p_qzq_type):
    con = get_connection()


    # 获取数据总条数
    total_count = con.execute("SELECT COUNT(*) "
                " FROM NW_SZY "
                "WHERE dwbh = $p_dwbh and model = $p_model "
                "  and ($p_qzq_type is null or qzq_type = $p_qzq_type) "
                " limit 1 ",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchone()[0]
    con.close()

    if total_count == 0:
        return 0
    else:
        return 1

def download_excel(p_dwbh, p_model, p_qzq_type):
    con = get_connection()
    # 将查询结果加载到 Pandas DataFrame
    df = con.execute("SELECT xhmc as 型号, fymc as 费用, je as 金额"
                " FROM NW_SZY "
                "WHERE dwbh = $p_dwbh and model = $p_model "
                "  and ($p_qzq_type is null or qzq_type = $p_qzq_type)"
                " order by fybh, xhbh",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchdf()

    # 查询费用类别

    df_col = con.execute("SELECT distinct fymc as c, fybh "
                " FROM NW_SZY "
                "WHERE dwbh = $p_dwbh and model = $p_model "
                "  and ($p_qzq_type is null or qzq_type = $p_qzq_type)"
                " order by fybh",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchdf()
    sorted_columns = df_col['c'].tolist()
    # print(df_col, type(df_col))

    # 设置自定义的 Excel 表头
    # 假设查询结果的列是 ['column1', 'column2', 'column3']，你可以自定义列名
    # custom_columns = ['型号', '费用', '金额']  # 自定义表头

    # 如果需要，可以重命名列
    # df.columns = custom_columns  # 将查询结果的列名替换为自定义列名

    df_pivot = df.pivot_table(index='型号', columns='费用', values='金额', aggfunc='sum')
    df_pivot = df_pivot.reindex(columns=sorted_columns)

    [need_report_desc] = ini_op.getinivalue('basic_config', 'need_report_desc')
    # print(need_report_desc)
    if need_report_desc == '是':
        # 查询列描述
        desc_row = {}
        for index, r_fybh  in enumerate(df_col['fybh'].tolist()):
            kmbh_desc = con.execute("SELECT STRING_AGG(DISTINCT COALESCE(kjnd||'年','')||kmbh_desc, ', ' ORDER BY kjnd nulls first) as kmbh_desc"
                                    "        FROM NW_FYMC "
                                 " WHERE dwbh = $p_dwbh and model = $p_model "
                                 "   and fybh = $p_fybh"
                                 "   and status = '1'",
                                 {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_fybh': r_fybh}).fetchone()[0]
            # print(index, r_fybh)
            desc_row[df_col['c'][index]] = kmbh_desc
        # print(desc_row)
        # 将自定义行插入到 DataFrame 中，放在标题行之后
        df_pivot = pd.concat([pd.DataFrame([desc_row], index=['-']), df_pivot])
        # 定义新的列名和数据（可以是任何长度匹配的列表、标量值或计算结果）
        desc_column = ['型号组成|列公式']

        # 查询行-型号描述
        df_xh = con.execute("""SELECT distinct xhbh, 
                         (select STRING_AGG(DISTINCT COALESCE(h.kjnd||'年:','')||COALESCE('+'||h.hsm_desc,'')||COALESCE('-'||h.hsm_desc2,''), ', ' ORDER BY kjnd nulls first)
                            from NW_PROJECT_DESC h 
                            where h.code = xhbh) hsm
                     FROM NW_SZY 
                    WHERE dwbh = $p_dwbh and model = $p_model 
                      and ($p_qzq_type is null or qzq_type = $p_qzq_type)
                     order by xhbh""",
                    {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchdf()
        sorted_xh = df_xh['hsm'].tolist()
        for c_xh in sorted_xh:
            # print(c_xh)
            desc_column.append(c_xh)
        # desc_column = ['型号组成|列公式', 'Data', 'Here','New', 'Data']

        # 将新列添加到 DataFrame 中
        # df_pivot[desc_column_name] = desc_column

        # 或者，如果要插入到特定位置（例如，在 'Column1' 和 'Column2' 之间），可以这样做：
        df_pivot.insert(loc=0, column='-', value=desc_column)



    ## 关闭连接
    con.close()

    # 将 DataFrame 转换为 Excel 文件（保存为 BytesIO 对象）
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df_pivot.to_excel(writer, index=True, header=True)  # 将透视表写入 Excel
        # 获取工作簿和工作表对象
        # workbook = writer.book
        worksheet = writer.sheets['Sheet1']
        worksheet.freeze_panes = 'C3' if need_report_desc == '是' else 'B2'


        # 设置列宽
        len_fybh = len(df_col['c'])
        # loop from A to H
        for col in string.ascii_uppercase[:(len_fybh+ (2 if need_report_desc == '是' else 1))]:
            worksheet.column_dimensions[col].width = 20


        # 设置行高
        worksheet.row_dimensions[1].height = 20
        light_blue_fill = PatternFill(start_color="ADD8E6", end_color="ADD8E6", fill_type="solid")

        # 设置边框
        thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                             top=Side(style='thin'), bottom=Side(style='thin'))
        # 设置第一行（标题行）的垂直居中对齐
        for cell in worksheet[1]:
            cell.alignment = Alignment(vertical='center', horizontal='center')
            cell.fill = light_blue_fill
            cell.border = thin_border  # 给所有单元格设置边框
        if need_report_desc == '是':
            # 设置第二行的垂直居中对齐
            worksheet.row_dimensions[2].height = 20
            for cell in worksheet[2]:
                cell.alignment = Alignment(vertical='center', horizontal='left')
                cell.fill = light_blue_fill
                cell.border = thin_border  # 给所有单元格设置边框
        # 设置某列字体加粗（例如：第一列字体加粗）
        for cell in worksheet['A']:
            cell.alignment = Alignment(vertical='center', horizontal='center')
            cell.fill = light_blue_fill
            cell.border = thin_border  # 给所有单元格设置边框
        if need_report_desc == '是':
            for cell in worksheet['B']:
                cell.alignment = Alignment(vertical='center', horizontal='left')
                cell.fill = light_blue_fill
                cell.border = thin_border  # 给所有单元格设置边框


    output.seek(0)


    excel_filename = 'szy.xlsx'  # 生成临时文件路径
    return [excel_filename, output]


